﻿CREATE TABLE [dbo].[Documente] (
    [IDDocument]           BIGINT           IDENTITY (1, 1) NOT NULL,
    [NrDoc]                VARCHAR (20)     NOT NULL,
    [DataDoc]              DATETIME         NOT NULL,
    [IDPartener]           BIGINT           NOT NULL,
    [CodUnitate]           SMALLINT         CONSTRAINT [DF_Documente_CodUnitate] DEFAULT ((0)) NOT NULL,
    [CotaTVA]              DECIMAL (5, 2)   CONSTRAINT [DF_Documente_CotaTVA] DEFAULT ((0)) NOT NULL,
    [IDTipDocument]        SMALLINT         CONSTRAINT [DF_Documente_IdTipDocument] DEFAULT ((0)) NOT NULL,
    [TotalValoare]         DECIMAL (25, 5)  CONSTRAINT [DF_Documente_TotalValoare] DEFAULT ((0)) NOT NULL,
    [TotalValoareTVA]      DECIMAL (25, 5)  CONSTRAINT [DF_Documente_TotalValoareTVA] DEFAULT ((0)) NOT NULL,
    [Total]                DECIMAL (25, 5)  CONSTRAINT [DF_Documente_Total] DEFAULT ((0)) NOT NULL,
    [IDRegimFiscal]        SMALLINT         CONSTRAINT [DF_Documente_IDRegimComercial] DEFAULT ((0)) NOT NULL,
    [UserId]               UNIQUEIDENTIFIER NULL,
    [IDDepartament]        INT              CONSTRAINT [DF_Documente_IDDepartament] DEFAULT ((0)) NULL,
    [NrContract]           VARCHAR (50)     NULL,
    [DataContract]         DATETIME         NULL,
    [Observatii]           VARCHAR (250)    NULL,
    [DataScadenta]         DATETIME         NULL,
    [ValoareTotalaCuAdaos] DECIMAL (25, 5)  CONSTRAINT [DF_Documente_ValoareTotalaCuAdaos] DEFAULT ((0)) NOT NULL,
    [NrDocReceptie]        VARCHAR (20)     NULL,
    [DataDocReceptie]      DATETIME         NULL,
    [IDGestionar]          INT              NULL,
    [IDPredator]           INT              NULL,
    [IDJurnal]             INT              NULL,
    [IDContactDelegat]     INT              NULL,
    [IDMijlocTransport]    SMALLINT         NULL,
    [CursValutar]          DECIMAL (18, 4)  DEFAULT ((0)) NOT NULL,
    [CodValuta]            SMALLINT         DEFAULT ((0)) NOT NULL,
    [AreDocGestStoc]       BIT              DEFAULT ((1)) NOT NULL,
    [DataStartProductie]   DATETIME         DEFAULT (getdate()) NOT NULL,
    [Serie]                VARCHAR (10)     NULL,
    [Numar]                VARCHAR (20)     NULL,
    [IDProgramSanatate]    SMALLINT         NULL,
    [DataCreation]         DATETIME         DEFAULT (getdate()) NOT NULL,
    [TotalValoareValuta]   DECIMAL (25, 5)  DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Documente] PRIMARY KEY CLUSTERED ([IDDocument] ASC),
    CONSTRAINT [FK_Documente_Contact] FOREIGN KEY ([IDContactDelegat]) REFERENCES [dbo].[Contact] ([IDContact]),
    CONSTRAINT [FK_Documente_Departamente] FOREIGN KEY ([IDDepartament]) REFERENCES [dbo].[Departamente] ([IDDepartament]),
    CONSTRAINT [FK_Documente_Gestionari] FOREIGN KEY ([IDGestionar]) REFERENCES [dbo].[Gestionari] ([IDGestionar]),
    CONSTRAINT [FK_Documente_Jurnale] FOREIGN KEY ([IDJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
    CONSTRAINT [FK_Documente_MijloaceTransport] FOREIGN KEY ([IDMijlocTransport]) REFERENCES [dbo].[MijloaceTransport] ([IDMijlocTransport]),
    CONSTRAINT [FK_Documente_Parteneri] FOREIGN KEY ([IDPartener]) REFERENCES [dbo].[Parteneri] ([IDPartener]),
    CONSTRAINT [FK_Documente_Predator] FOREIGN KEY ([IDPredator]) REFERENCES [dbo].[Gestionari] ([IDGestionar]),
    CONSTRAINT [FK_Documente_ProgrameSanatate] FOREIGN KEY ([IDProgramSanatate]) REFERENCES [dbo].[TipProgrameSanatate] ([IDTipProgram]),
    CONSTRAINT [FK_Documente_RegimFiscal] FOREIGN KEY ([IDRegimFiscal]) REFERENCES [dbo].[RegimFiscal] ([IDRegimFiscal]),
    CONSTRAINT [FK_Documente_TipDocument] FOREIGN KEY ([IDTipDocument]) REFERENCES [dbo].[TipDocument] ([IDTipDocument]),
    CONSTRAINT [FK_Documente_Unitati] FOREIGN KEY ([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
    CONSTRAINT [FK_Documente_Valuta] FOREIGN KEY ([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta]),
    CONSTRAINT [Dublare_Documente_Furnizor_Numar_Data] UNIQUE NONCLUSTERED ([IDPartener] ASC, [CodUnitate] ASC, [NrDoc] ASC, [DataDoc] ASC)
);


GO


CREATE TRIGGER [dbo].[audit_Document] 
   ON  [dbo].[Documente]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @modifDoc bit
	SELECT @modifDoc = SalveazaCuUrmatorulNumar FROM FaresSetup a JOIN inserted i ON
		a.[CodUnitate]=i.CodUnitate
	IF @modifDoc=1
		UPDATE [Documente] SET NrDoc = (SELECT MAX(CAST([NrDoc] AS decimal(18,0)))+1 FROM [Documente] a WHERE 
			a.CodUnitate=[Documente].[CodUnitate] AND a.IDTipDocument = [Documente].IDTipDocument) FROM inserted i
		WHERE i.IDDocument=[Documente].[IDDocument]  AND 
		NOT EXISTS(SELECT * FROM deleted d WHERE d.IDDocument=i.IDDocument)

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT [UserID],'Documente', 'A', IDDocument, getdate() FROM inserted i
		WHERE i.IDDocument not in (SELECT IDDocument from deleted)	 

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT i.[UserID],'Documente', 'M', i.IDDocument, getdate() FROM inserted i
		JOIN deleted d on i.IDDocument=d.IDDocument

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT [UserID],'Documente', 'D', IDDocument, getdate() FROM deleted d
		WHERE d.IDDocument not in (SELECT IDDocument from inserted)	 

	

END